/* RETRIEVE DISCREPENCY ONLY */

SELECT COUNT(*)
FROM 
(
	(SELECT *
	FROM
		(SELECT	BG.OLD_BG_NAME O_OLD_BG_NAME,			-- 
				BG.NEW_BG_NAME O_NEW_BG_NAME,			--
				S.APPLICATION_ID O_APPLICATION_ID, 
				S.ID_FLEX_CODE O_ID_FLEX_CODE,
				S.ID_FLEX_NUM O_ID_FLEX_NUM, 
				F.ID_FLEX_NAME O_ID_FLEX_NAME, 
				S.ID_FLEX_STRUCTURE_CODE O_ID_FLEX_STRUCTURE_CODE, 
				ST.LANGUAGE O_LANGUAGE, 
				ST.ID_FLEX_STRUCTURE_NAME O_ID_FLEX_STRUCTURE_NAME,
				NVL(ST.DESCRIPTION, 'NULLNULL') O_DESCRIPTION, 
				NVL(S.STRUCTURE_VIEW_NAME, 'NULLNULL') O_STRUCTURE_VIEW_NAME, 
				NVL(S.FREEZE_FLEX_DEFINITION_FLAG, 'N') O_FREEZE_FLEX_DEFINITION_FLAG,
				NVL(S.CROSS_SEGMENT_VALIDATION_FLAG, 'N') O_CROSS_SEG_VALIDATION_FLAG, 
				NVL(S.ENABLED_FLAG, 'N') O_ENABLED_FLAG, 
				NVL(S.FREEZE_STRUCTURED_HIER_FLAG, 'N') O_FREEZE_STRUCTURED_HIER_FLAG,
				NVL(S.DYNAMIC_INSERTS_ALLOWED_FLAG, 'N') O_DYNAMIC_INSERTS_ALLOWED_FLAG
		FROM	FND_ID_FLEXS				F, 
				FND_ID_FLEX_STRUCTURES		S, 
				FND_ID_FLEX_STRUCTURES_TL	ST, 
				CRST_BG_INFO				BG								--
		WHERE	S.APPLICATION_ID	=	ST.APPLICATION_ID
		AND		S.ID_FLEX_CODE		=	ST.ID_FLEX_CODE
		AND		S.ID_FLEX_NUM		=	ST.ID_FLEX_NUM
		AND		F.APPLICATION_ID	=	S.APPLICATION_ID
		AND		F.ID_FLEX_CODE		=	S.ID_FLEX_CODE
		AND		((ST.ID_FLEX_STRUCTURE_NAME	LIKE	BG.OLD_BG_NAME||'%')		--
				OR	(BG.NEW_BG_NAME	=	'C077'									-- 
				AND	BG.OLD_BG_NAME	=	'C048'									-- 
				AND	ST.ID_FLEX_STRUCTURE_NAME	LIKE	'C048%')
				)
		)	OLD_KFF,
		(SELECT	BG.OLD_BG_NAME N_OLD_BG_NAME,			-- 
				BG.NEW_BG_NAME N_NEW_BG_NAME,			--
				S.APPLICATION_ID N_APPLICATION_ID, 
				S.ID_FLEX_CODE N_ID_FLEX_CODE,
				S.ID_FLEX_NUM N_ID_FLEX_NUM, 
				F.ID_FLEX_NAME N_ID_FLEX_NAME, 
				--
				DECODE(CRST_COMMON_PCKG.IS_DEBUG_MODE, 'Y',
						REPLACE(S.ID_FLEX_STRUCTURE_CODE, BG.NEW_BG_NAME, BG.OLD_BG_NAME),
						REPLACE(S.ID_FLEX_STRUCTURE_CODE, BG.NEW_BG_NAME, BG.OLD_BG_NAME)
				) N_ID_FLEX_STRUCTURE_CODE, 
				ST.LANGUAGE N_LANGUAGE, 
				DECODE(CRST_COMMON_PCKG.IS_DEBUG_MODE, 'Y',
					REPLACE(ST.ID_FLEX_STRUCTURE_NAME, BG.NEW_BG_NAME, BG.OLD_BG_NAME),
					REPLACE(ST.ID_FLEX_STRUCTURE_NAME, BG.NEW_BG_NAME, BG.OLD_BG_NAME)
				) N_ID_FLEX_STRUCTURE_NAME,
				NVL(ST.DESCRIPTION, 'NULLNULL') N_DESCRIPTION, 
				DECODE(CRST_COMMON_PCKG.IS_DEBUG_MODE, 'Y',
					REPLACE(NVL(S.STRUCTURE_VIEW_NAME, 'NULLNULL'), BG.NEW_BG_NAME, BG.OLD_BG_NAME),
					REPLACE(NVL(S.STRUCTURE_VIEW_NAME, 'NULLNULL'), BG.NEW_BG_NAME, BG.OLD_BG_NAME)
				) N_STRUCTURE_VIEW_NAME, 
				--
				NVL(S.FREEZE_FLEX_DEFINITION_FLAG, 'N') N_FREEZE_FLEX_DEFINITION_FLAG,
				NVL(S.CROSS_SEGMENT_VALIDATION_FLAG, 'N') N_CROSS_SEG_VALIDATION_FLAG, 
				NVL(S.ENABLED_FLAG, 'N') N_ENABLED_FLAG, 
				NVL(S.FREEZE_STRUCTURED_HIER_FLAG, 'N') N_FREEZE_STRUCTURED_HIER_FLAG,
				NVL(S.DYNAMIC_INSERTS_ALLOWED_FLAG, 'N') N_DYNAMIC_INSERTS_ALLOWED_FLAG
		FROM	FND_ID_FLEXS				F, 
				FND_ID_FLEX_STRUCTURES		S, 
				FND_ID_FLEX_STRUCTURES_TL	ST, 
				CRST_BG_INFO				BG							--
		WHERE	S.APPLICATION_ID	=	ST.APPLICATION_ID
		AND		S.ID_FLEX_CODE		=	ST.ID_FLEX_CODE
		AND		S.ID_FLEX_NUM		=	ST.ID_FLEX_NUM
		AND		F.APPLICATION_ID	=	S.APPLICATION_ID
		AND		F.ID_FLEX_CODE		=	S.ID_FLEX_CODE
		AND		((ST.ID_FLEX_STRUCTURE_NAME LIKE BG.NEW_BG_NAME||'%')			--
				OR	(BG.NEW_BG_NAME='C077'										-- 
					AND	BG.OLD_BG_NAME='C048'									-- 
					AND	ST.ID_FLEX_STRUCTURE_NAME LIKE BG.NEW_BG_NAME||'%')		--
				)
		)	NEW_KFF
	WHERE	OLD_KFF.O_APPLICATION_ID	=	NEW_KFF.N_APPLICATION_ID(+)
	AND		OLD_KFF.O_ID_FLEX_CODE		=	NEW_KFF.N_ID_FLEX_CODE(+)
	AND		OLD_KFF.O_LANGUAGE			=	NEW_KFF.N_LANGUAGE(+)
	)
UNION
	(SELECT *
	FROM
		(SELECT	BG.OLD_BG_NAME O_OLD_BG_NAME,				-- 
				BG.NEW_BG_NAME O_NEW_BG_NAME,				--
				S.APPLICATION_ID O_APPLICATION_ID, 
				S.ID_FLEX_CODE O_ID_FLEX_CODE,
				S.ID_FLEX_NUM O_ID_FLEX_NUM, 
				F.ID_FLEX_NAME O_ID_FLEX_NAME, 
				S.ID_FLEX_STRUCTURE_CODE O_ID_FLEX_STRUCTURE_CODE, 
				ST.LANGUAGE O_LANGUAGE, 
				ST.ID_FLEX_STRUCTURE_NAME O_ID_FLEX_STRUCTURE_NAME,
				NVL(ST.DESCRIPTION, 'NULLNULL') O_DESCRIPTION, 
				NVL(S.STRUCTURE_VIEW_NAME, 'NULLNULL') O_STRUCTURE_VIEW_NAME, 
				NVL(S.FREEZE_FLEX_DEFINITION_FLAG, 'N') O_FREEZE_FLEX_DEFINITION_FLAG,
				NVL(S.CROSS_SEGMENT_VALIDATION_FLAG, 'N') O_CROSS_SEG_VALIDATION_FLAG, 
				NVL(S.ENABLED_FLAG, 'N') O_ENABLED_FLAG, 
				NVL(S.FREEZE_STRUCTURED_HIER_FLAG, 'N') O_FREEZE_STRUCTURED_HIER_FLAG,
				NVL(S.DYNAMIC_INSERTS_ALLOWED_FLAG, 'N') O_DYNAMIC_INSERTS_ALLOWED_FLAG
		FROM	FND_ID_FLEXS				F, 
				FND_ID_FLEX_STRUCTURES		S, 
				FND_ID_FLEX_STRUCTURES_TL	ST, 
				CRST_BG_INFO				BG
		WHERE	S.APPLICATION_ID	=	ST.APPLICATION_ID
		AND	S.ID_FLEX_CODE		=	ST.ID_FLEX_CODE
		AND	S.ID_FLEX_NUM		=	ST.ID_FLEX_NUM
		AND	F.APPLICATION_ID	=	S.APPLICATION_ID
		AND	F.ID_FLEX_CODE		=	S.ID_FLEX_CODE
		AND	((ST.ID_FLEX_STRUCTURE_NAME	LIKE	BG.OLD_BG_NAME||'%')		--
			OR	(BG.NEW_BG_NAME	=	'C077'									-- 
				AND	BG.OLD_BG_NAME	=	'C048'								-- 
				AND	ST.ID_FLEX_STRUCTURE_NAME	LIKE	'C048%')
			)
		)	OLD_KFF,
		(SELECT	BG.OLD_BG_NAME N_OLD_BG_NAME,				-- 
				BG.NEW_BG_NAME N_NEW_BG_NAME,				--
				S.APPLICATION_ID N_APPLICATION_ID, 
				S.ID_FLEX_CODE N_ID_FLEX_CODE,
				S.ID_FLEX_NUM N_ID_FLEX_NUM, 
				F.ID_FLEX_NAME N_ID_FLEX_NAME, 
				--
				DECODE(CRST_COMMON_PCKG.IS_DEBUG_MODE, 'Y',
					REPLACE(S.ID_FLEX_STRUCTURE_CODE, BG.NEW_BG_NAME, BG.OLD_BG_NAME),
					REPLACE(S.ID_FLEX_STRUCTURE_CODE, BG.NEW_BG_NAME, BG.NEW_BG_NAME)
				)	N_ID_FLEX_STRUCTURE_CODE, 
				ST.LANGUAGE N_LANGUAGE, 
				DECODE(CRST_COMMON_PCKG.IS_DEBUG_MODE, 'Y', 
					REPLACE(ST.ID_FLEX_STRUCTURE_NAME, BG.NEW_BG_NAME, BG.OLD_BG_NAME),
					REPLACE(ST.ID_FLEX_STRUCTURE_NAME, BG.NEW_BG_NAME, BG.OLD_BG_NAME)
				)	N_ID_FLEX_STRUCTURE_NAME,
				NVL(ST.DESCRIPTION, 'NULLNULL') N_DESCRIPTION, 
				DECODE(CRST_COMMON_PCKG.IS_DEBUG_MODE, 'Y',
					REPLACE(NVL(S.STRUCTURE_VIEW_NAME, 'NULLNULL'), BG.NEW_BG_NAME, BG.OLD_BG_NAME),
					REPLACE(NVL(S.STRUCTURE_VIEW_NAME, 'NULLNULL'), BG.NEW_BG_NAME, BG.OLD_BG_NAME)
				)	N_STRUCTURE_VIEW_NAME, 
				--
				NVL(S.FREEZE_FLEX_DEFINITION_FLAG, 'N') N_FREEZE_FLEX_DEFINITION_FLAG,
				NVL(S.CROSS_SEGMENT_VALIDATION_FLAG, 'N') N_CROSS_SEG_VALIDATION_FLAG, 
				NVL(S.ENABLED_FLAG, 'N') N_ENABLED_FLAG, 
				NVL(S.FREEZE_STRUCTURED_HIER_FLAG, 'N') N_FREEZE_STRUCTURED_HIER_FLAG,
				NVL(S.DYNAMIC_INSERTS_ALLOWED_FLAG, 'N') N_DYNAMIC_INSERTS_ALLOWED_FLAG
		FROM	FND_ID_FLEXS F, 
				FND_ID_FLEX_STRUCTURES S, 
				FND_ID_FLEX_STRUCTURES_TL ST, 
				CRST_BG_INFO BG						--
		WHERE	S.APPLICATION_ID	=	ST.APPLICATION_ID
		AND		S.ID_FLEX_CODE		=	ST.ID_FLEX_CODE
		AND		S.ID_FLEX_NUM		=	ST.ID_FLEX_NUM
		AND		F.APPLICATION_ID	=	S.APPLICATION_ID
		AND		F.ID_FLEX_CODE		=	S.ID_FLEX_CODE
		AND		((ST.ID_FLEX_STRUCTURE_NAME LIKE BG.NEW_BG_NAME||'%')			--
				OR	(BG.NEW_BG_NAME		=	'C077'								-- 
					AND	BG.OLD_BG_NAME	=	'C048'								-- 
					AND	ST.ID_FLEX_STRUCTURE_NAME	LIKE	BG.NEW_BG_NAME||'%')	--
				)
		)	NEW_KFF
	WHERE	OLD_KFF.O_APPLICATION_ID(+)	=	NEW_KFF.N_APPLICATION_ID
	AND		OLD_KFF.O_ID_FLEX_CODE(+)	=	NEW_KFF.N_ID_FLEX_CODE
	AND		OLD_KFF.O_LANGUAGE(+)		=	NEW_KFF.N_LANGUAGE
	)
)
WHERE	NVL(O_APPLICATION_ID, 9999999999)||
		NVL(O_ID_FLEX_CODE, 'NULLNULL')||
		NVL(O_ID_FLEX_NAME, 'NULLNULL')||
		NVL(O_ID_FLEX_STRUCTURE_CODE, 'NULLNULL')||
		NVL(O_LANGUAGE, 'NULLNULL')||
		NVL(O_ID_FLEX_STRUCTURE_NAME, 'NULLNULL')||
		NVL(O_DESCRIPTION, 'NULLNULL')||
		NVL(O_STRUCTURE_VIEW_NAME, 'NULLNULL')||
		NVL(O_FREEZE_FLEX_DEFINITION_FLAG, 'NULLNULL')||
		NVL(O_CROSS_SEG_VALIDATION_FLAG, 'NULLNULL')||
		NVL(O_ENABLED_FLAG, 'NULLNULL')||
		NVL(O_FREEZE_STRUCTURED_HIER_FLAG, 'NULLNULL')||
		NVL(O_DYNAMIC_INSERTS_ALLOWED_FLAG, 'NULLNULL')
		<>
		NVL(N_APPLICATION_ID, 9999999999)||
		NVL(N_ID_FLEX_CODE, 'NULLNULL')||
		NVL(N_ID_FLEX_NAME, 'NULLNULL')||
		NVL(N_ID_FLEX_STRUCTURE_CODE, 'NULLNULL')||
		NVL(N_LANGUAGE, 'NULLNULL')||
		NVL(N_ID_FLEX_STRUCTURE_NAME, 'NULLNULL')||
		NVL(N_DESCRIPTION, 'NULLNULL')||
		NVL(N_STRUCTURE_VIEW_NAME, 'NULLNULL')||
		NVL(N_FREEZE_FLEX_DEFINITION_FLAG, 'NULLNULL')||
		NVL(N_CROSS_SEG_VALIDATION_FLAG, 'NULLNULL')||
		NVL(N_ENABLED_FLAG, 'NULLNULL')||
		NVL(N_FREEZE_STRUCTURED_HIER_FLAG, 'NULLNULL')||
		NVL(N_DYNAMIC_INSERTS_ALLOWED_FLAG, 'NULLNULL')
/


